﻿using Rsd.Dudu;
using Rsd.Dudu.Core;
using Rsd.Faith.Bank.Core;
using Rsd.Faith.Core;
using Rsd.Faith.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;

namespace Rsd.Faith.Bank.Services
{
    /// <summary>
    /// 
    /// </summary>
    public class BankService : BaseService, IBankService
    {
        public override ServiceProxcy Proxcy =>  ServiceProxcy.Sington;

        /// <summary>
        /// 根据存款计划 执行存款
        /// </summary>
        /// <param name="cunKuan"></param>
        /// <returns></returns>
        public int BankSavings(BankCunKuanJiHuaModel cunKuan)
        {
            var rs = 0;
            if (cunKuan.ZhuangTai == CunKuanZhuangTai.DJ)
            {
                rs = this.BankSavings(cunKuan.SuoShuQi, cunKuan.KaiShiRiQi, cunKuan.JieShuRiQi, cunKuan.DingQiLiLv, cunKuan.HuoQiLiLv, cunKuan.DingCunBiLi);
                if (rs > 0)
                {
                    cunKuan.ZhuangTai = CunKuanZhuangTai.YC;
                    cunKuan.SetState(Dudu.BizModel.ObjectStateType.Update);
                    MyType.SaveObjects(cunKuan);
                }
                
            }

            return rs;
        }

        /// <summary>
        ///  执行存款 分户账目报表产生后 才可执行
        /// </summary>
        /// <param name="suoShuQi"></param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <param name="dingQiLilv"></param>
        /// <param name="huoQiLilv"></param>
        /// <param name="dingCunBili"></param>
        /// <returns></returns>
        private int BankSavings(string suoShuQi, DateTime beginDate, DateTime endDate, decimal dingQiLilv, decimal huoQiLilv, int dingCunBili)
        {
            //var count = (int)MyType.GetDBOperator<BookFenHuZhangModel>().Count().Where(obj => obj.SuoShuQi != suoShuQi).Execute();
            //if (count > 0)
            //{
            //    throw new Exception("分户所属期不一致，无法存款");
            //}


            var _end = endDate.AddDays(1);
            //往年 缴存的余额，按比例 存 定期和活期 存款

            //本期起初金额 分户  report__fen_hu_zhang_mu 表 ,通过 qi_chu_jin_e 获取上期余额

            var sql_cunkuan = string.Format(@"insert into bank__fenhu_shou_yi_biao{0} (id,`xiao_qu_bian_hao`, `zhang_hu_bian_hao`, `fang_wu_bian_hao`,`suo_shu_qi`,`kai_shi_ri_qi`,`jie_shu_ri_qi`,`ding_qi_li_lv`,`huo_qi_li_lv`,`ding_cun_bi_lv`,`ding_qi_li_xi`,`huo_qi_li_xi`, `li_xi_he_ji` ,`qi_chu_ben_jin`,`zhuang_tai` )
SELECT id,`xiao_qu_bian_hao`, `zhang_hu_bian_hao`, `fang_wu_bian_hao`, '{0}' , '{1}', '{2}', {3},{4},{5},CONVERT((qi_chu_jin_e * {3} * ({5}/100))/100,DECIMAL(10,2)),CONVERT((qi_chu_jin_e*{4}* ({6}/100))/100,DECIMAL(10,2)),0,`qi_chu_jin_e`,'{6}' from report__fen_hu_zhang_mu{0} where qi_chu_jin_e > 0;", suoShuQi, beginDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), dingQiLilv, huoQiLilv, dingCunBili, 100 - dingCunBili, LiXiShouYiZhuangTai.DJ.ToString());

            var dbSession = (IDBSession)this.GetService(typeof(IDBSession), null);

            return dbSession.DbService.Execute(new string[] { sql_cunkuan });
        }

        /// <summary>
        /// 核算 存款利息 分户账目报表产生后 才可执行
        /// </summary>
        /// <param name="cunKuan"></param>
        public int SettleInterest(BankCunKuanJiHuaModel cunKuan)
        {
            ////检查所属期
            //var count = (int)MyType.GetDBOperator<BookFenHuZhangModel>().Count().Where(obj => obj.SuoShuQi != cunKuan.SuoShuQi).Execute();
            //if (count > 0)
            //{
            //    throw new Exception("分户所属期不一致，无法核算");
            //}

            //本期（所属期）缴存的本金， 仅活期 存款  以缴纳日期 开始计息
            var sql_huo_qi_cunkuan = string.Format(@"insert into bank__fenhu_shou_yi_biao{0} (id,`xiao_qu_bian_hao`, `fang_wu_bian_hao`,`suo_shu_qi`,`kai_shi_ri_qi`,`jie_shu_ri_qi`,`ding_qi_li_lv`,`huo_qi_li_lv`,`ding_cun_bi_lv`,`ding_qi_li_xi`,`huo_qi_li_xi`, `li_xi_he_ji` ,`qi_chu_ben_jin`,`zhuang_tai` )
SELECT id,`xiao_qu_bian_hao`, `fang_wu_dai_ma`, '{0}' , jiao_na_ri_qi, '{2}', {3},{4},{5},CONVERT((DAYOFYEAR(date_format('{0}-12-31','%y-%m-%d')) - DAYOFYEAR ( jiao_na_ri_qi ))/DAYOFYEAR(date_format('{0}-12-31','%y-%m-%d')) * (shi_jiao_jin_e*{3}*({5}/100))/100,DECIMAL(10,2)),CONVERT((DAYOFYEAR(date_format('{0}-12-31','%y-%m-%d')) - DAYOFYEAR ( jiao_na_ri_qi )) / DAYOFYEAR(date_format('{0}-12-31','%y-%m-%d')) * (shi_jiao_jin_e*{4}*({6}/100))/100,DECIMAL(10,2)),0,`shi_jiao_jin_e`,'{6}' from fiscal__piao_ju_xin_xi_biao where  `suo_shu_qi` = '{0}' and zhuang_tai like 'RZ';", cunKuan.SuoShuQi, cunKuan.KaiShiRiQi.ToString("yyyy-MM-dd"), cunKuan.JieShuRiQi.ToString("yyyy-MM-dd"), cunKuan.DingQiLiLv, cunKuan.HuoQiLiLv, 0, 100, LiXiShouYiZhuangTai.DJ.ToString());


            // 根据本期 内支出流水 记账日期 ，根据分摊数据 计算 分户在所属期内使用金额应扣除的利息  

            //  求和 ：（使用金额）  * 利率  （365-jiZhangRiQi.DayOfYear）/ 当年总天数
            var sql_lixi_hesuan = string.Format(@"update bank__fenhu_shou_yi_biao{0} A ,
(
select c.fang_wu_bian_hao,SUM(IFNULL(d.huo_qi_li_xi,0) * (c.total - c.days) / c.total) as 'jin_e' from (SELECT fang_wu_bian_hao, DAYOFYEAR(date_format('{0}-12-31','%y-%m-%d')) as 'total', DAYOFYEAR ( hua_bo_ri_qi ) as 'days',fen_tan_jin_e ,hua_bo_ri_qi as 'jin_e'  from biz__zi_jin_shi_yong_fen_tan_biao  where suo_shu_qi = '{0}') c 
LEFT JOIN bank__fenhu_shou_yi_biao{0} d on c.fang_wu_bian_hao = d.fang_wu_bian_hao GROUP BY c.fang_wu_bian_hao 
) B 
set A.huo_qi_li_xi_kc = B.jin_e where A.fang_wu_bian_hao = B.fang_wu_bian_hao;", cunKuan.SuoShuQi);

            //  更新 合计 并 更新状态
            var sql_update_li_xi_he_ji = string.Format("UPDATE bank__fenhu_shou_yi_biao{0} set li_xi_he_ji =ding_qi_li_xi + huo_qi_li_xi-huo_qi_li_xi_kc, zhuang_tai = '{1}';", cunKuan.SuoShuQi, LiXiShouYiZhuangTai.HS.ToString());
            //  更新 zhang_hu_bian_hao 
            var sql_update_zhang_hu_bian_hao = string.Format(@"update bank__fenhu_shou_yi_biao{0} A LEFT JOIN(SELECT xiao_qu_bian_hao, fang_wu_bian_hao, zhang_hu_bian_hao from book__fen_hu_zhang) B
 on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao and A.fang_wu_bian_hao = B.fang_wu_bian_hao set A.zhang_hu_bian_hao = B.zhang_hu_bian_hao;", cunKuan.SuoShuQi);//(A.zhang_hu_bian_hao = '' or A.zhang_hu_bian_hao is null) 
            // 更新存款计划表信息
            var sql_update_ji_hua = string.Format("update bank__cun_kuan_ji_hua A, (select sum(li_xi_he_ji) as 'li_xi'  from bank__fenhu_shou_yi_biao{0}) B set A.ying_jie_li_xi = B.li_xi where A.id = '{1}';", cunKuan.SuoShuQi, cunKuan.Id);
            
            var dbSession = (IDBSession)this.GetService(typeof(IDBSession), null);

            var rs = dbSession.DbService.Execute(new string[] { sql_huo_qi_cunkuan, sql_lixi_hesuan, sql_update_li_xi_he_ji, sql_update_ji_hua });
            
            return rs;
        }

        /// <summary>
        /// 指定存款计划 指定分户 结息入账 记流水
        /// </summary>
        /// <param name="cunKuan"></param>
        /// <param name="list">空 表示全部 </param>
        /// <returns></returns>
        public int SettleRecording(BankCunKuanJiHuaModel cunKuan)
        {
            //var count = (int)MyType.GetDBOperator<BookFenHuZhangModel>().Count().Where(obj => obj.SuoShuQi != cunKuan.SuoShuQi).Execute();
            //if (count > 0)
            //{
            //    throw new Exception("分户所属期不一致，无法结息");
            //}

            var sqls = new List<string>();

             
            //利息分户入账 记入流水  摘要:"结息(所属期：" + suoShuQi + ")" 状态必须是 已核算 
            var sql_fen_hu_liu_shui =  string.Format(@"INSERT INTO book__fen_hu_zhang_liu_shui (id,liu_shui_hao,suo_shu_qi,jin_e,yu_e,shou_ru__zhi_chu,ri_qi,zhai_yao,zhang_hu_bian_hao,remark) SELECT concat('{2}',id),  CONCAT('LS{0}JX',fang_wu_bian_hao) 'liu_shui_hao',suo_shu_qi,
li_xi_he_ji,0,'收入',DATE_FORMAT(jie_shu_ri_qi, '%Y-%m-%d'),'{0}所属期分户结息',zhang_hu_bian_hao,fang_wu_bian_hao from bank__fenhu_shou_yi_biao{0} where suo_shu_qi = '{0}' and zhuang_tai = '{1}' and id in (SELECT id from bank__fenhu_shou_yi_biao{0} where concat('{2}',id) not in (SELECT id from book__fen_hu_zhang_liu_shui)); ", cunKuan.SuoShuQi , LiXiShouYiZhuangTai.HS.ToString(),"LX"+ cunKuan.SuoShuQi.Substring(cunKuan.SuoShuQi.Length-2));
            //同步流水账户编号
            var sql_update_fenhu_book_liu_shui = @"update book__fen_hu_zhang_liu_shui a ,book__fen_hu_zhang b set a.zhang_hu_bian_hao = b.zhang_hu_bian_hao where b.fang_wu_bian_hao = a.remark and a.zhang_hu_bian_hao is null or a.zhang_hu_bian_hao='';";

            //核算分户账目 增值收益  shou_yi_jin_e
            //新建 属期分户账目 report__fen_hu_zhang_mu{0}
            var sql_insert_fenhu_report = string.Format("insert into report__fen_hu_zhang_mu{0} (id,suo_shu_qi,zhang_hu_bian_hao ,xiao_qu_bian_hao ,fang_wu_bian_hao) SELECT id,'{0}' as 'suo_shu_qi', zhang_hu_bian_hao,xiao_qu_bian_hao,fang_wu_bian_hao  from book__fen_hu_zhang where id not in (SELECT id from book__fen_hu_zhang );", cunKuan.SuoShuQi);
             //核算 分户帐目 report__fen_hu_zhang_mu{0}
            var sql_update_fentan_report = string.Format("update report__fen_hu_zhang_mu{0} A,bank__fenhu_shou_yi_biao{0} B set A.shou_yi_jin_e = B.li_xi_he_ji where A.fang_wu_bian_hao = B.fang_wu_bian_hao;", cunKuan.SuoShuQi);
             
            // 更新状态
            var sql_update_shou_yi = string.Format("UPDATE bank__fenhu_shou_yi_biao{0} set zhuang_tai = '{1}';", cunKuan.SuoShuQi, LiXiShouYiZhuangTai.JX.ToString());

            //更新存款计划状态:已结利息 已入账 
            var sql_update_ji_hua = string.Format("update bank__cun_kuan_ji_hua A, (select sum(li_xi_he_ji) as 'li_xi'  from bank__fenhu_shou_yi_biao{0}) B set A.yi_jie_li_xi = B.li_xi where A.id = '{1}';", cunKuan.SuoShuQi, cunKuan.Id);

            sqls.AddRange(new string[] { sql_fen_hu_liu_shui, sql_update_fenhu_book_liu_shui,sql_insert_fenhu_report, sql_update_fentan_report, sql_update_shou_yi, sql_update_ji_hua });


            var dbSession = (IDBSession)this.GetService(typeof(IDBSession), null);
            var rs = dbSession.DbService.Execute(sqls.ToArray());
             
            return rs;
        }

        /// <summary>
        /// 核算各账户 分户利息金额
        /// </summary>
        /// <param name="suoShuQi"></param>
        /// <returns></returns>
        public int SumBookInterest()
        {
            // 核算小区\楼栋\单元 账户 分户利息合计金额

            //合计分户各属期利息 金额 shou_yi_jin_e = sum(li_xi_he_ji) bank__fenhu_shou_yi_biao 
            var flag = false;
            var sql_fen_hu_li_xi = new StringBuilder();
            sql_fen_hu_li_xi.Append("update book__fen_hu_zhang A LEFT JOIN (select zhang_hu_bian_hao, sum(li_xi_he_ji) as 'li_xi_he_ji' from(");
            for (var year = 2000; year < DateTime.Now.Year; year++)
            {
                if (flag)
                {
                    sql_fen_hu_li_xi.AppendFormat(" union ");
                }
                sql_fen_hu_li_xi.AppendFormat("select '{0}' as 'suo_shu_qi', zhang_hu_bian_hao, sum(li_xi_he_ji) as 'li_xi_he_ji' from bank__fenhu_shou_yi_biao{0} where zhuang_tai = '{1}' GROUP BY zhang_hu_bian_hao", year, LiXiShouYiZhuangTai.JX.ToString());
                flag = true;
            }
            sql_fen_hu_li_xi.Append(" ) B GROUP BY B.zhang_hu_bian_hao) C ON A.zhang_hu_bian_hao = C.zhang_hu_bian_hao set A.shou_yi_jin_e = C.li_xi_he_ji;");
            
            //合计小区 分户利息fen_hu_li_xi = sum(shou_yi_jin_e)
            var sql_xiao_qu_lixi = "update book__xiao_qu_zong_zhang A  LEFT JOIN (select xiao_qu_bian_hao ,sum(shou_yi_jin_e) as 'fen_hu_li_xi' from book__fen_hu_zhang group by xiao_qu_bian_hao ) B on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao set A.fen_hu_li_xi=B.fen_hu_li_xi;";
             
            //合计楼栋 分户利息fen_hu_li_xi = sum(shou_yi_jin_e)
            var sql_lou_dong_lixi = @"update book__lou_dong_ming_xi_zhang A  
LEFT JOIN(select D.xiao_qu_bian_hao, D.lou_dong_hao, sum(C.shou_yi_jin_e) as 'fen_hu_li_xi' from book__fen_hu_zhang C LEFT JOIN info__fang_wu_xin_xi_biao D on C.fang_wu_bian_hao = D.bian_hao group by D.xiao_qu_bian_hao,D.lou_dong_hao) B
 on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao  and A.lou_dong_hao = B.lou_dong_hao set A.fen_hu_li_xi = B.fen_hu_li_xi; ";

            //合计单元 分户利息fen_hu_li_xi = sum(shou_yi_jin_e)
            var sql_dan_yuan_lixi = @"update book__dan_yuan_ming_xi_zhang A  
LEFT JOIN (select D.xiao_qu_bian_hao,D.lou_dong_hao,D.dan_yuan_hao, sum(C.shou_yi_jin_e) as 'fen_hu_li_xi' from book__fen_hu_zhang C LEFT JOIN info__fang_wu_xin_xi_biao D on C.fang_wu_bian_hao = D.bian_hao group by D.xiao_qu_bian_hao,D.lou_dong_hao,D.dan_yuan_hao) B 
on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao  and  A.lou_dong_hao = B.lou_dong_hao and A.dan_yuan_hao= B.dan_yuan_hao set A.fen_hu_li_xi=B.fen_hu_li_xi;";
            
            var dbSession = (IDBSession)this.GetService(typeof(IDBSession), null);

            var rs = dbSession.DbService.Execute(new string[] { sql_fen_hu_li_xi.ToString(), sql_xiao_qu_lixi, sql_lou_dong_lixi, sql_dan_yuan_lixi });

            return rs;
        }

        /// <summary>
        ///  核算指定属期账目 分户利息金额
        /// </summary>
        /// <param name="suoShuQi"></param>
        /// <returns></returns>
        public int SumReportInterest(string suoShuQi)
        {
            //合计总账目
            var sql_zz = string.Format("update  report__zi_jin_zong_zhang A ,( select  sum(li_xi_he_ji) as 'li_xi_he_ji' from bank__fenhu_shou_yi_biao{0} where zhuang_tai = '{1}') B  set A.shou_yi_jin_e_zz = B.li_xi_he_ji WHERE A.suo_shu_qi = '{0}';", suoShuQi, LiXiShouYiZhuangTai.JX.ToString());
            //合计小区账目
            var sql_xiaoqu = string.Format("update report__xiao_qu_zhang_mu A LEFT JOIN(select xiao_qu_bian_hao, sum(li_xi_he_ji) as 'li_xi_he_ji' from bank__fenhu_shou_yi_biao{0} where zhuang_tai = '{1}' GROUP BY xiao_qu_bian_hao) B on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao set A.shou_yi_jin_e_zz = B.li_xi_he_ji WHERE A.suo_shu_qi = '{0}'; ", suoShuQi, LiXiShouYiZhuangTai.JX.ToString());
            //合计楼栋账目
            var sql_loudong = string.Format(@"update report__lou_dong_zhang_mu A  
LEFT JOIN (select D.xiao_qu_bian_hao,D.lou_dong_hao,sum(C.li_xi_he_ji) as 'li_xi_he_ji' from  bank__fenhu_shou_yi_biao{0} C  LEFT JOIN info__fang_wu_xin_xi_biao D on C.fang_wu_bian_hao = D.bian_hao where C.zhuang_tai = '{1}'  group by D.xiao_qu_bian_hao,D.lou_dong_hao) B 
on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao  and  A.lou_dong_hao = B.lou_dong_hao set A.shou_yi_jin_e_zz=B.li_xi_he_ji where A.suo_shu_qi='{0}';", suoShuQi, LiXiShouYiZhuangTai.JX.ToString());
            //合计单元账目
            var sql_danyuan = string.Format(@"update report__dan_yuan_zhang_mu A  
LEFT JOIN (select D.xiao_qu_bian_hao,D.lou_dong_hao,D.dan_yuan_hao, sum(C.li_xi_he_ji) as 'li_xi_he_ji' from  bank__fenhu_shou_yi_biao{0} C  LEFT JOIN info__fang_wu_xin_xi_biao D on C.fang_wu_bian_hao = D.bian_hao where C.zhuang_tai = '{1}'  group by D.xiao_qu_bian_hao,D.lou_dong_hao,D.dan_yuan_hao) B 
on A.xiao_qu_bian_hao = B.xiao_qu_bian_hao  and  A.lou_dong_hao = B.lou_dong_hao and A.dan_yuan_hao= B.dan_yuan_hao set A.shou_yi_jin_e_zz=B.li_xi_he_ji where A.suo_shu_qi='{0}';", suoShuQi, LiXiShouYiZhuangTai.JX.ToString());

            var dbSession = (IDBSession)this.GetService(typeof(IDBSession), null);
            var rs = dbSession.DbService.Execute(new string[] { sql_zz, sql_xiaoqu, sql_loudong, sql_danyuan });

            return rs;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public override dynamic GetMonitorInfo()
        {
            throw new NotImplementedException();
        }
    }
}
